/*=========================================================================
Elite College as Engines of Upward Mobility: Evidence from Colombia's Ser Pilo Paga
Authors: Juliana Londoño-Vélez, Catherine Rodriguez, Fabio Sánchez
and Luis Esteban Álvarez-Arango

Creation date: June 6th, 2025
--------------------------------------------------------------------------
Figure A14
=========================================================================*/

import excel "${raw}/1.2.5.IPC_Serie_variaciones.xlsx", sheet("Sheet1") firstrow clear
rename (MesAño Indice Variaciónanual Variaciónmensual Variaciónañocorrido) (month_year indice variacion_anual variacion_mensual variacion_anual_corrido)
tostring month_year , replace 
gen month = substr(month_year, -2, 2)
gen year = substr(month_year , 1, 4)
keep indice month year 

collapse (mean) indice, by(year)
replace indice = indice/100
destring year, replace 

tempfile ipc_2018
save `ipc_2018'


use "${raw}/20230331_valor_matricula_programlevel_anual_least5years_2012_2018_balanced", clear 

gen ies_acreditada = 0
replace ies_acreditada = 1 if (cod_ies=="1101" | cod_ies=="1102" | cod_ies=="1103" | cod_ies=="1104" | cod_ies=="1106" | cod_ies=="1107" ///
| cod_ies=="1108" | cod_ies=="1109" | cod_ies=="1110" | cod_ies=="1111" | cod_ies=="1112" | cod_ies=="1124" ///
| cod_ies=="1125" | cod_ies=="1126" | cod_ies=="1201" | cod_ies=="1203" | cod_ies=="1204" | cod_ies=="1205" ///
| cod_ies=="1701" | cod_ies=="1702" | cod_ies=="1704" | cod_ies=="1705" | cod_ies=="1706" | cod_ies=="1707" ///
| cod_ies=="1710" | cod_ies=="1711" | cod_ies=="1712" | cod_ies=="1713" | cod_ies=="1714" | cod_ies=="1803" ///
| cod_ies=="1812" | cod_ies=="1813" | cod_ies=="1823" | cod_ies=="1828" | cod_ies=="1830" | cod_ies=="1832" ///
| cod_ies=="2106" | cod_ies=="2708" | cod_ies=="2812" | cod_ies=="2813" | cod_ies=="3114"  | cod_ies=="3302" | cod_ies=="3710")

sum matricula_estnuevos if  regexm(nombre_ies, "UNIVERSIDAD DEL VALLE")
replace matricula_estnuevos = r(mean) if regexm(nombre_ies, "UNIVERSIDAD DEL VALLE") & ano == 2017 

gen matricula_estnuevos_2 = matricula_estnuevos 

collapse (mean) matricula_estnuevos (median) matricula_estnuevos_2 , by(ano sector ies_acreditada)

rename ano year 

merge m:1 year using `ipc_2018' , keepusing(indice) keep(3) nogen 

gen matricula_estnuevos_real = matricula_estnuevos/indice 
gen matricula_estnuevos_2_real = matricula_estnuevos_2/indice

tostring year, replace  
destring year, replace  

gen post = (year>=2015)

gen privada = (sector == "PRIVADA")
gen ies_acreditada_privada = (sector == "PRIVADA" & ies_acreditada==1)

forvalues y = 2012/2018 {
	qui gen ies_acreditada_privada_`y' = ies_acreditada_privada if year == `y'
	qui replace ies_acreditada_privada_`y' = 0 if ies_acreditada_privada_`y' == . & ies_acreditada_privada != . 
}

replace ies_acreditada_privada_2014 = 0

local d = 2012
foreach x in ies_acreditada_privada_2012 ies_acreditada_privada_2013 ies_acreditada_privada_2014 ies_acreditada_privada_2015 ies_acreditada_privada_2016 ies_acreditada_privada_2017 ies_acreditada_privada_2018 {
	label var `x' "`d'"
	local d=`d'+1
}
 
tw  (line matricula_estnuevos_real year if ies_acreditada == 1 & sector == "OFICIAL" , lc(black)) ///
(line matricula_estnuevos_real year if ies_acreditada == 1 & sector == "PRIVADA" , lc(gray)) ///
(line matricula_estnuevos_real year if ies_acreditada == 0 & sector == "OFICIAL" , lc(black) lp(-)) ///
(line matricula_estnuevos_real year if ies_acreditada == 0 & sector == "PRIVADA" , lc(gray) lp(-)) ///
, graphr(c(white)lc(white)) plotregion(fcolor(white) lc(white))  ///
xtitle("Year") ytitle("Average Tuition Cost (New Students)") ///
ylabel(#6, angle(0) grid glp(dot) glc(black*0.2) labc(black) labs(small) ) ///
xlabel(#10, angle(0) grid glp(dot) glc(black*0.2) labc(black) labs(small) ) ///
legend(off order(1 "High Quality - Public" 2 "High Quality - Private" 3 "Low Quality - Public" 4 "Low Quality - Private") rows(2) size(small) region(lcolor(white)))
graph export "${outcomes}/FigA14.pdf", replace width(20)
